package com.davidhigenbottam.HeatMap.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;

import com.davidhigenbottam.HeatMap.shared.Institution;
import com.davidhigenbottam.HeatMap.shared.Query;


public class StaffWithPhdInstitutionData extends InstitutionData {

	public StaffWithPhdInstitutionData(Query query) {
		super(query);
		getInstitutionsFromDatabase();
	}



	public void getInstitutionsFromDatabase() {
		Connection c = null;
		try {
			c = getConnection();
			
						

			String statement = "select gi.name, gi.acronym, gi.url, gi.geocode1, gi.private , sum(psg.total) as sumtotal  "
					+ "from phd_staff_group as psg, geocoded_institution as gi "
					+ "where gi.institution_id = psg.inst_id "
					+ "group by psg.inst_id "
					+ "order by sumtotal desc ";
			log.info("statement:"+statement);
			
			PreparedStatement stmt = c.prepareStatement(statement);
			
			if (stmt.execute()) {
				ResultSet rs = stmt.getResultSet();
				while (rs.next()) {
					String name = rs.getString("name");
					String acronym = rs.getString("acronym");
					String url = rs.getString("url");
					String geocode= rs.getString("geocode1");
					String privateInst= rs.getString("private");
					
					Long sumTotal = rs.getLong("sumtotal");
					
					Institution i = new Institution( name, acronym ,url, geocode, privateInst, Double.valueOf(sumTotal.doubleValue()));
			
					addInstitution(i);
				}
			}
		} catch (SQLException e) {
			log.log(Level.SEVERE, "SQLException", e);
			throw new RuntimeException(e);
		} finally {
			if (c != null)
				try {
					c.close();
				} catch (SQLException ignore) {
				}
		}
		
	}


	

}
